package com.sg.java.apps;

import com.sg.java.PropertiesUtil;
import com.sg.java.ResourcePath;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.Pipeline;

import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/**
 * 数据中台台账数据入redis做电压数据计算时候要用的关联关系
 */
public class EssentialData {

    private static final Logger log = LoggerFactory.getLogger(EssentialData.class);


    public static void main(String[] args) throws Exception {
        log.info("从数据中台dws读取用户关联关系数据存入redis");
        Properties dwsDataProp = PropertiesUtil.createAndLoadPropertiesFromFileOrResource(null, ResourcePath.dws_data_jdbc_properties);
        log.info("数据中台dws配置：" + dwsDataProp.toString());
        Properties redisProp = PropertiesUtil.createAndLoadPropertiesFromFileOrResource(null, ResourcePath.redis_properties);
        log.info("redis配置：" + redisProp.toString());
        try (
                Jedis jedis = new Jedis(redisProp.getProperty("host"), Integer.parseInt(redisProp.getProperty("port")), 1000 * 60);
                Connection conn = DriverManager.getConnection(
                        dwsDataProp.getProperty("url"),
                        dwsDataProp.getProperty("user"),
                        dwsDataProp.getProperty("password")
                )
        ) {
            jedis.auth(redisProp.getProperty("password"));
            //开通管道
            Pipeline jp = jedis.pipelined();
//            redisSetAc(jp);
            jedis.select(0);


            String filePath = "/tmp/yhgx.csv";
            //初始化csvformat
            //withRecordSeparator 行分隔符,默认就是 \n
            //withDelimiter 字段分隔符
            CSVFormat formator = CSVFormat.DEFAULT
                    .withRecordSeparator("\n")
                    .withDelimiter(',');
            FileOutputStream fos = new FileOutputStream(filePath);
            OutputStreamWriter fileWriter = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
            CSVPrinter printer = new CSVPrinter(fileWriter, formator);


            PreparedStatement ps;
            int batchSize = 500000;
            int index = 0;
            boolean hasMore = true;
            while (hasMore) {
                int count = 0;
                String sql = getYhsql(batchSize, index);
                ps = conn.prepareStatement(sql);
                log.info(ps.toString());
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {

                    String s = rs.getString(5).replaceAll(",", "");
                    String key = "YHGX@" + rs.getString(3);
                    String value = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(4) + "," + s + "," + rs.getString(6) + ",01";
//                    jedis.set(key, value);

//                    jp.set(key, value);
                    String gridType = "";
                    String type = rs.getString(6);
                    if (StringUtils.isEmpty(type) || type.equals("02") || type.equals("03")) {
                        gridType = "2";
                    } else if (type.equals("01")) {
                        gridType = "1";
                    }

                    String v = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "," + rs.getString(4) + "," + s + "," + gridType + ",01";
                    printer.printRecord(v);

                    count++;
                }
                //管道异步执行
//                jp.sync();
                index += batchSize;
                hasMore = count == batchSize;
            }

            log.info("全部同步结束");
            jp.close();
            jedis.close();
            printer.flush();
            printer.close();
        }
    }

    private static String getYhsql(int batchSize, int index) {


        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select dd.cons_id, dd.volt_code, dd.meter_id , dd.ORG_NO,dd.CONS_NAME,dd.urban_rural_flag  from ( " +
                " select bb.CONS_ID, bb.volt_code, bb.meter_id, bb.ORG_NO, bb.CONS_NAME, bb.urban_rural_flag from( " +
                " select cons.CONS_ID, cons.volt_code, rela.meter_id, cons.ORG_NO, cons.CONS_NAME, cons.urban_rural_flag " +
                "  from un_cms.un14_02_cms_c_cons_cqzj as cons," +
                "  un_cms.un14_02_cms_c_meter_mp_rela_cqzj as rela," +
                "  un_cms.un14_02_cms_c_mp_cqzj as mp," +
                "  un_cms.un14_02_cms_c_meter as meter" +
                "  WHERE" +
                "  cons.cons_id = mp.cons_id" +
                "  and cons.STATUS_CODE <> '9'" +
                "  and rela.mp_id = mp.mp_id" +
                "  and rela.meter_id = meter.meter_id" +
                "  and mp.volt_code = cons.VOLT_CODE" +
                "  and mp.meas_mode in('1')" +
                "  and(mp.mp_level = '1'or mp.mp_level = '0')" +
                "  and meter.ref_meter_flag <> '01'" +
                "  and mp.status_code in ('02', '01')" +
                "  and SUBSTR (cons.CONS_SORT_CODE, 1, 2) ='01'" +
                "  and mp.USAGE_TYPE_CODE = '01'" +
                "  and cons.VOLT_CODE IN" +
                "  ('AC00061', 'AC00101', 'AC01381', 'AC00201', 'AC02751', 'AC00351', 'AC00661', 'AC01101', 'AC02201', 'AC03301', 'AC05001', 'DC05001', 'AC07501', 'DC08001', 'AC10001') )" +
                "  bb" +
                " UNION ALL" +
                " select aa.CONS_ID, aa.volt_code, aa.meter_id, aa.ORG_NO, aa.CONS_NAME, aa.urban_rural_flag from(" +
                " select" +
                " cons.CONS_ID, cons.volt_code, rela.meter_id, cons.ORG_NO, cons.CONS_NAME, cons.urban_rural_flag" +
                " from" +
                " un_cms.un14_02_cms_c_cons_cqzj cons," +
                " un_cms.un14_02_cms_c_meter_mp_rela_cqzj rela," +
                " un_cms.un14_02_cms_c_mp_cqzj mp," +
                " un_cms.un14_02_cms_c_meter meter" +
                " WHERE" +
                "  cons.cons_id = mp.cons_id" +
                "  and cons.STATUS_CODE <> '9'" +
                "  and rela.mp_id = mp.mp_id" +
                "  and rela.meter_id = meter.meter_id" +
                "  and mp.volt_code = cons.VOLT_CODE" +
                " and mp.usage_type_code = '01'" +
                " and mp.type_code = '01'" +
                " and mp.mp_level = '1'" +
                " and meter.ref_meter_flag<> '01'" +
                "  and mp.status_code in ('02', '01')" +
                " AND mp.USAGE_TYPE_CODE = '01'" +
                " AND SUBSTR (cons.CONS_SORT_CODE, 1, 2) <>'01'" +
                "  AND cons.VOLT_CODE IN" +
                " ('AC00062'," +
                " 'DC00062'," +
                " 'AC00122'," +
                " 'DC00122'," +
                " 'AC00242'," +
                " 'DC00242'," +
                " 'AC00362'," +
                " 'DC00362'," +
                " 'AC00482'," +
                " 'DC00482'," +
                " 'AC01102'," +
                " 'DC01102'," +
                " 'AC02202'," +
                " 'DC02202'," +
                " 'AC03802')" +
                " )aa" +
                ")  dd  order by meter_id  limit " + batchSize + " offset " + index);

        return stringBuilder.toString();


    }


}
